package com.example.mysql.学习笔记.高级.数据库的优化之索引.数据库调优的其他策略;

public class 优化数据库结构 {
}
/**
 * 3.优化数据库结构
 * 一个好的数据库设计方案对于数据库的性能常常会起到事半功倍的效果。.
 * 合理的数据库结构不仅可以使数据库占用更小的磁盘空间，而且能够使查询速度更快。
 * 数据库结构的设计需要考虑数据冗余、查询和更新的速度、 字段的数据类型是否合理等多方面的内容。
 * 3.1拆分表:冷热数据分离
 * 3.2增加中间表
 * 3.3增加冗余字段
 * 3.4优化数据类型
 * 3.5优化插入记录的速度
 * 3.6使用非空约束
 * 3.7分析表、检查表与优化表
 *     1.分析表
 *     2.检查表
 *     3.优化表
 *
 */
/**
 * 3.1拆分表:冷热数据分离
 * 拆分表的思路是，把1个包含很多字段的表拆分成2个或者多个相对较小的表。
 * 这样做的原因是，这些表中某些字段的操作频率很高(热数据)，经常要进行查询或者更新操作，
 * 而另外一些字段的使用频率却很低(冷数据)，冷热数据分离，可以减小表的宽度。
 * 如果放在一个表里面，每次查询都要读取大记录，会消耗较多的资源。
 *
 * MySQL限制每个表最多存储4096列， 并且每一 行数据的大小不能超过65535字节。 表越宽，把表装载进内存缓
 * 冲池时所占用的内存也就越大,也会消耗更多的I/0。 冷热数据分离的目的是:
 * ①减少磁盘I/O,保证热数据的内存缓存命中率。
 * ②更有效的利用缓存，避免读入无用的冷数据。
 *
 */
/**
 *3.2增加中间表
 * 对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插
 * 入中间表中，然后将原来的联合查询改为对中间表的查询。以此来提高查询效率.
 * 首先，分析经常联合查询表中的字段;然后,使用这些字段建立一个中间表,
 * 并将原来联合查询的表的数据插入中间表中;
 * 最后，使用中间表来进行查询。
 *
 */
/**
 *3.3增加冗余字段
 * 设计数据库表时应尽量遵循范式理论的规约,尽可能减少冗余字段,让数据库设计看起来精致、优雅。
 * 但是,合理地加入冗余字段可以提高查询速度。
 * 表的规范化程度越高，表与表之间的关系就越多,需要连接查询的情况也就越多。
 * 尤其在数据量大，而且需要频繁进行连接的时候，为了提升效率,我们也可以考虑增加冗余字段来减少连接。
 * 这部分内容在《第11章_数据库的设计规范》章节中反范式化小节中具体展开讲解了。这里省略。
 *
 *
 */
/**
 * 3.4优化数据类型
 * 改进表的设计时，可以考虑优化字段的数据类型。这个问题在大家刚从事开发时基本不算是问题。
 * 但是，随着你的经验越来越丰富，参与的项目越来越大，数据量也越来越多的时候，
 * 你就不能只从系统稳定性的角度来思考问题了，还要考虑到系统整体的稳定性和效率。
 * 此时，优先选择符合存储需要的最小的数据类型。
 * 列的字段越大，建立索引时所需要的空间也就越大，这样一页中所能存储的索引节点的数量也就越少，
 * 在遍历时所需要的I/0次数也就越多，索引的性能也就越差。
 * 情况1:对整数类型数据进行优化。 默认用INT
 *      对于非负型的数据(如自增ID、整型IP)来说，要优先使用无符号整型 UNSIGNED 来存储。
 *      因为无符号相对于有符号，同样的字节数，存储的数值范围更大。
 *      如tinyint有符号为-128-127, 无符号为0-255,多出一倍的存储空间。
 * 情况2:既可以使用文本类型也可以使用整数类型的字段，要选择使用整数类型。
 *       将IP地址转换成整型数据
 * 情况3:避免使用TEXT、BLOB数据类型
 *       MySQL内存临时表不支持TEXT、BLOB这样的大数据类型
 *       如果一定要使用，建议把BLOB或是TEXT列分离到单独的扩展表中，查询时一定不要使用select *
 *       而只需要取出必要的列，不需要TEXT列的数据时不要对该列进行查询。
 * 情况4:避免使用 ENUM 类型
 *      修改 ENUM 值需要使用 ALTER 语句。
 *      ENUM类型的ORDER BY(排序)操作效率低，需要额外操作。使用TINYINT（小整数）来代替ENUM类型。
 * 情况5:使用TIMESTAMP存储时间
 *       TIMESTAMP存储的时间范围1970-01-01 00:00:01 ~ 2038-01-19-03:14:07。
 *       TIMESTAMP使用4字节， DATETIME使用8个字节，同时TIMESTAMP具有自动赋值以及自动更新的特性。
 * 情况6:用DECIMAL代替FL0AT和DOUBLE存储精确浮点数
 *      1)非精准浮点: float,double
 *      2)精准浮点: decimal
 *      Decimal类型为精准浮点数，在计算时不会丢失精度,尤其是财务相关的金融类数据。
 *      占用空间由定义的宽度决定，每4个字节可以存储9位数字,并且小数点要占用一个字节。
 *      可用于存储比bigint更大的整型数据。
 *
 * 总之,遇到数据量大的项目时，- -定要在充分了解业务需求的前提下，合理优化数据类型，
 * 这样才能充分发挥资源的效率，使系统达到最优。
 */
/**
 * 3.5优化插入记录的速度
 * 插入记录时，影响插入速度的主要是索引、唯一性校验、一次插入记录条数等。
 * 根据这些情况可以分别进行优化。这里我们分为MyISAM引擎和InnoDB存储引擎来讲。
 * 1. MyISAM引擎的表:
 * ①禁用索引
 * 对于非空表，插入记录时，MySQL会根据表的索引对插入的记录建立索引。
 * 如果插入大量数据，建立索引就会降低插入记录的速度。
 * 为了解决这种情况，可以在插入记录之前禁用索引，数据插入完毕后再开启索引。
 * 禁用索引的语句如下:
 * ALTER TABLE table_name DISABLE KEYS;
 * 重新开启索引的语句如下:
 * ALTER TABLE table_name ENABLE KEYS;
 * 若对于空表批量导入数据，则不需要进行此操作，因为MyISAM引擎的表是在导入数据之后才建立索引的。
 *
 * ②禁用唯一性检查
 * 插入数据时，MySQL会对插入的记录进行唯一性校验。 这种唯一性校验会降低插入记录的速度。
 * 为了降低这种情况对查询速度的影响，可以在插入记录之前禁用唯一性检查, 等到记录插入完毕后再开启。
 * 禁用唯一性检查的语句如下:
 * SET UNIQUE_CHECKS=0;
 * 开启唯一性检查的语句如下:
 * SET UNIQUE_CHECKS=1 ;
 * ③使用批量插入
 * 插入多条记录时，可以使用一条INSERT语句插入一条记录， 也可以使用一条INSERT语句插入多条记录。
 * ④使用LOAD DATA INFILE批量导入
 * 当需要批量导入数据时，如果能用 LOAD DATA INFILE 语句，就尽量使用。
 * 因为 LOAD DATA INFILE 语句导入数据的速度比INSERT语句快。
 *
 * 2. InnoDB引擎的表:
 * ①禁用唯一性检查
 * 插入数据之前执行set unique_checks=0 来禁止对唯一索引的检查,
 * 数据导入完成之后再运行set unique_checks=1. 这个和MyISAM引擎的使用方法--样。
 * ②禁用外键检查
 * 插入数据之前执行禁止对外键的检查，数据插入完成之后再恢复对外键的检查。禁用外键检查的语句如下:
 * SET foreign_key_checks=0 ;
 * 恢复对外键的检查语句如下:
 * SET foreign_key_checks=1 ;
 * ③禁止自动提交
 * 插入数据之前禁止事务的自动提交，数据导入完成之后，执行恢复自动提交操作。
 * 禁止自动提交的语句如下:
 * set autocommit=0;
 * 恢复自动提交的语句如下:
 * set autocommit=1 ;
 *
 */
/**
 *3.6 使用非空约束
 * 在设计字段的时候，如果业务允许,建议尽量使用非空约束。这样做的好处是:
 * ①进行比较和计算时，省去要对NULL值的字段判断是否为空的开销，提高存储效率。
 * ②非空字段也容易创建索引。因为索引NULL列需要额外的空间来保存，所以要占用更多的空间。
 *   使用非空约束,就可以节省存储空间(每个字段1个bit)。
 */
/**
 * 3.7分析表、检查表与优化表
 * MySQL提供了     分析表、检查表和优化表的语句。
 * 分析表 主要是分析关键字的分布，
 * 检查表 主要是检查表是否存在错误，
 * 优化表 主要是消除删除或者更新造成的空间浪费。
 *
 * #分析表  ANALYZE TABLE 表名;//会更新 SHOW  INDEX FROM 表名 的数据
 *   SHOW  INDEX FROM 表名;//显示来自的索引
 *   使用ANALYZE TABLE 分析表的过程中，数据库系统会自动对表加一个只读锁。
 *   在分析期间，只能读取表中的记录,不能更新和插入记录。
 *   ANALYZE TABLE语句能够分析InnoDB和MyISAM类型的表,但是不能作用于视图。
 *   ANALYZE TABLE分析后的统计结果会反应到cardinality(此索引有多少不同的值)的值，
 *   该值统计了表中某一键所在的列不重复的值的个数。
 *   该值越接近表中的总行数，则在表连接查询或者索引查询时，就越优先被优化器选择使用。
 *   也就是索引列的cardinality的值与表中数据的总条数差距越大，
 *   即使查询的时候使用了该索引作为查询条件,存储弓|擎实际查询的时候使用的概率就越小。
 *
 * 2.检查表
 * MySQL中可以使用CHECK TABLE 语句来检查表。
 * CHECK TABLE语句能够检查InnoDB和MyISAM类型的表是否存在错误。
 * CHECK TABLE语句在执行过程中也会给表加上只读锁。
 * 对于MyISAM类型的表，CHECK TABLE语句还会更新关键字统计数据。
 * 而且，CHECK TABLE也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在。
 *
 * 3.优化表
 * 方式1: OPTIMIZE TABLE
 * MySQL中使用OPTIMIZE TABLE 语句来优化表。
 * 但是，OPTILMIZE TABLE语句只能优化表中的VARCHAR、BLOB或TEXT类型的字段。
 * --个表使用了这些字段的数据类型,若已经删除了表的一大部分数据，
 * 或者已经对含有可长度行的表(含有VARCHAR、 BLOB或TEXT列的表) 进行了很多更新，
 * 则应使用OPTIMIZE TABLE来重新利用未使用的空间，并整理数据文件的碎片。
 * 说明:
 * 在多数的设置中，根本不需要运行 OPTIMIZE TABLE。
 * 即使对可变长度的行进行了大量的更新，也不需要经常运行，
 * 每周一次或每月一次即可，并且只需要对特定的表运行。
 *  他会进行碎片的整理，减少内存消耗
 *
 *
 */
/**
 * 3.8小结
 * 上述这些方法都是有利有弊的。比如:
 * ● 修改数据类型，节省存储空间的同时，你要考虑到数据不能超过取值范围;
 * ● 增加冗余字段的时候，不要忘了确保数据一致性;
 * ● 把大表拆分，也意味着你的查询会增加新的连接，从而增加额外的开销和运维的成本。
 *   因此，你一定要结合实际的业务需求进行权衡。
 *
 */